// SQL Select builder datapump implementation
// Copyright 2006 (C) Ralph Thomas

#include <datapumps/sql_select.h>
#include <datapumps/dictionary_helper.h>
#include <string>
#include <adobe/array.hpp>
#include <binder.hpp>
#include <boost/bind.hpp>
#include <util/db.h>
#include <vector>
#include <sstream>
#include <algorithm>
#include <boost/algorithm/string.hpp>
#include <toolkit/application.h>
#include <util/concepts/list.h>

#include <iostream>

namespace datapumps {
	struct sql_column_t {
		std::string column_m;

		sql_column_t( const std::string& column ) : column_m( column ) {}
		sql_column_t( const sql_column_t& rhs ) :column_m( rhs.column_m) {}

		bool operator==( const sql_column_t& rhs ) const { return rhs.column_m == column_m; }
	};

	typedef boost::weak_ptr<adobe::binder_t> weak_binder_t;

	struct sql_select_datapump_t : public adobe::binder_t {
		typedef					std::vector<adobe::binder_helper_t*> helper_list_t;
		std::string				columns_m;
		std::string				table_m;
		std::string				where_m;
		std::string				order_m;
		adobe::dictionary_t		where_ops_m;
		adobe::dictionary_t		where_dict_m;
		dictionary_helper_t		where_helper_m;
		dictionary_helper_t		where_ops_helper_m;
		adobe::binder_helper_t	columns_datapump_m;
		adobe::binder_helper_t	table_datapump_m;
		adobe::binder_helper_t	order_datapump_m;
		adobe::sheet_t&			sheet_m;
		adobe::sheet_t::monitor_value_t	value_monitor_m;
		adobe::dictionary_t		params_m;
		bool					dirty_m;
		weak_binder_t			weak_m;
		typedef boost::shared_ptr<sql_select_datapump_t> us_ptr_t;

		void _update() {
			adobe::binder_ptr_t us(weak_m.lock());
			if( dirty_m || !us ) return;
			dirty_m = true;
			toolkit::application::getInstance().executeInMain(boost::bind(&sql_select_datapump_t::_check_update, weak_m));
		}

		static void _check_update(weak_binder_t weak_us) {
			adobe::binder_ptr_t us(weak_us.lock());
			if (!us) return;
			us_ptr_t us_type = boost::dynamic_pointer_cast<sql_select_datapump_t>( us );
			us_type->_do_update();
		}

		void _do_update() {
			if (!dirty_m) return;
			std::stringstream query;
			query << "select " << columns_m << " from " << table_m;
			if (!where_m.empty())
				query << " where " << where_m;
			if (!order_m.empty())
				query << " order by " << order_m;
			query << ";";
			dirty_m = false;
			if (value_monitor_m)
				value_monitor_m(adobe::value_t(query.str()));
		}

		static std::string name_to_sql_column(const adobe::name_t& name)
		{
			std::string column = name.get();
			boost::replace_all(column, "__", ".");
			return column;
		}

		static void _comma_append(std::string& s, const adobe::value_t& val, bool quote_strings)
		{
			if (val.type() == typeid(std::string))
			{
				if( quote_strings ) {
					s += "'" + util::db::escape(val.get<std::string>()) + "', ";
				} else s += val.get<std::string>() + ", ";
			}
			else if( val.type() == typeid(double))
			{
				std::stringstream ss;
				ss << val.get<int>() << ", ";
				s += ss.str();
			}
		}
		
		std::string _comma_list(const adobe::array_t& a, bool quote_strings = false)
		{
			std::string out;
			for_each(a.begin(), a.end(),
				boost::bind(&_comma_append, boost::ref(out), _1, quote_strings));
			return out.substr(0, out.size() - 2);
		}
		std::string _comma_list(const util::concept::List l, bool quote_strings = false)
		{
			std::string out;
			for (unsigned int i = 0; i < l.size(); i++)
				_comma_append(out, l.at(i), quote_strings);
			return out;//out.substr(0, out.size() - 2);
		}
		
		void _update_simple(std::string& s, const adobe::value_t& c)
		{
			if( c.type() == typeid(std::string) )
			{
				s = c.get<std::string>();
				_update();
				return;
			}
			//
			// This is invalid. Don't do any processing.
			// 
			if( c.type() != typeid(adobe::array_t) ) return;
			//
			// Get the array and build the list of columns.
			// 
			s = _comma_list( c.get<adobe::array_t>() );
			_update();
		}

		void _update_where_dict( const adobe::dictionary_t& d )
		{
			where_dict_m = d;
			_update_where();
		}

		void _update_where_ops( const adobe::dictionary_t& d )
		{
			where_ops_m = d;
			_update_where();
		}

		void _update_where()
		{
			//
			// This function interprets names as SQL column names. It uses
			// name_to_sql_column to do so (and it just replaces underscores
			// with dots).
			//
			// We skip past names that have empty values.
			//
			where_m.clear();
			std::stringstream ss;
			adobe::dictionary_t::const_iterator i = where_dict_m.begin();
			while (i != where_dict_m.end()) {
				if( i->second == adobe::value_t() ) continue;
				if( i != where_dict_m.begin() ) ss << "and ";
				ss << name_to_sql_column( i->first );
				std::string op = "=";
				if( where_ops_m.count( i->first ) )
					op = where_ops_m[i->first].get<std::string>();
				if (i->second.type() == typeid(std::string)) {
					ss << " " << op << " \"" << i->second.get<std::string>() << "\" ";
				} else if (i->second.type() == typeid(adobe::array_t)) {
					ss << " in (" << _comma_list(i->second.get<adobe::array_t>(), true) << ") ";
				} else if (i->second.type() == typeid(double)) {
					ss << " " << op << " " << i->second.get<int>() << " ";
				} else if (i->second.type() == typeid(sql_column_t)) {
					ss << " " << op << " " << i->second.get<sql_column_t>().column_m << " ";
				} else {
					util::concept::List l(util::concept::get_list(i->second));
					ss << " in (" << _comma_list(l, true) << ") ";
				}
				i++;
			}
			where_m = ss.str();
			_update();
		}

		void set_weak_ptr(adobe::binder_ptr_t& bin) {
			weak_m = weak_binder_t(bin);
			dirty_m = false;
			_update();
		}

		sql_select_datapump_t(const adobe::dictionary_t& params, adobe::sheet_t& sheet)
		 : where_helper_m(sheet), where_ops_helper_m(sheet), columns_datapump_m(sheet),
		   table_datapump_m(sheet), order_datapump_m(sheet), sheet_m(sheet), params_m(params), dirty_m(false)
		{
			adobe::static_name_t key_where("where");
			adobe::static_name_t key_where_ops("where_ops");
			adobe::static_name_t key_columns("columns");
			adobe::static_name_t key_table("table");
			adobe::static_name_t key_order("order");

			//
			// The "where", "where_ops" and "order" parameters are optional.
			// 
			if (params.count(key_where))
				where_helper_m.initialize(params.at(key_where),
					boost::bind( &sql_select_datapump_t::_update_where_dict, this, _1 ));
			if (params.count(key_where_ops))
				where_ops_helper_m.initialize(params.at(key_where_ops),
					boost::bind( &sql_select_datapump_t::_update_where_ops, this, _1));
			//
			// "columns" and "table" are mandatory.
			// 
			columns_datapump_m.initialize(params.at(key_columns),
				boost::bind( &sql_select_datapump_t::_update_simple, this, boost::ref(columns_m), _1), 0);
			table_datapump_m.initialize(params.at(key_table),
				boost::bind( &sql_select_datapump_t::_update_simple, this, boost::ref(table_m), _1), 0);
			//
			// "order" optional.
			//
			if (params.count(key_order))
				order_datapump_m.initialize(params.at(key_order),
					boost::bind( &sql_select_datapump_t::_update_simple, this, boost::ref(order_m), _1), 0);
		}
		void set(const adobe::value_t&) {}
		void set_signals(const adobe::sheet_t::monitor_value_t& monitor_value, const adobe::sheet_t::monitor_active_t& monitor_active)
		{
			if (monitor_active) monitor_active(false);
			value_monitor_m = monitor_value;
			_update();
		}
		void substitute(const adobe::name_t& name, const adobe::value_t& value)
		{
			where_helper_m.substitute(name, value);
			columns_datapump_m.substitute(name, value);
			table_datapump_m.substitute(name, value);
			order_datapump_m.substitute(name, value);
		}
		void invoke() {}
		adobe::binder_ptr_t copy() const {
			sql_select_datapump_t* dp(new sql_select_datapump_t( params_m, sheet_m ));
			adobe::binder_ptr_t b(dp);
			dp->set_weak_ptr(b);
			return b;
		}
	};
	adobe::value_t make_sql_select_datapump( const adobe::dictionary_t& params, adobe::sheet_t& sheet )
	{
		//
		// This is kind of hackish. We need a good way for giving a datapump
		// a weak pointer to itself. This is something that happens frequently
		// enough that it would be good to come up with "the way" to do it.
		//
		sql_select_datapump_t* dp(new sql_select_datapump_t(params, sheet));
		adobe::binder_ptr_t b(dp);
		dp->set_weak_ptr(b);
		return adobe::value_t(b);
	}
	adobe::value_t make_sql_column( const adobe::array_t& array )
	{
		if (array.size() != 1 || array[0].type() != typeid(std::string))
			throw std::runtime_error( "sql_column: argument error" );
		return adobe::value_t(sql_column_t(array[0].get<std::string>()));
	}
}
